package com.xisoft.handle;

import com.xisoft.base.BaseModel;
import com.xisoft.base.Column;
import com.xisoft.base.Table;
import com.xisoft.common.ProcessorException;
import com.xisoft.common.UnicodeUtil;
import com.xisoft.config.DBConfig;

import java.lang.reflect.Field;
import java.sql.*;


/**
 * @author xzh
 * @Date 2021/8/16
 * @description 数据库处理器：
 * 注意：本内容仅限于上海熙软科技有限公司内部，传阅，禁止外泄以及用于其他商业目的
 */
public class DBHandler implements Handler {


    @Override
    public void handle(BaseModel model, HandlerChain chain) throws ProcessorException {
        DBConfig dbConfig = model.dbConfig;
        Table table = null;

        StringBuilder sql1 = new StringBuilder();
        sql1.append(" SELECT");
        sql1.append(" t.table_name dname,");
        sql1.append(" replace(initcap(lower(substr(t.table_name, instr(t.table_name, '_')))), '_', '') vname,");
        sql1.append(" t.table_type,");
        sql1.append(" nvl(t.comments, '.') comments");
        sql1.append(" FROM");
        sql1.append(" user_tab_comments t");
        sql1.append(" WHERE");
        sql1.append(" table_name = upper('");
        sql1.append(model.tablename);
        sql1.append("')");

        StringBuilder sql2 = new StringBuilder();
        sql2.append(" SELECT t.column_name dname,");
        sql2.append(" lower(t.column_name) vname,");
        sql2.append(" CASE WHEN substr(t.data_type, 1, 9) = 'TIMESTAMP' ");
        sql2.append(" THEN substr(t.data_type, 1, 9) ");
        sql2.append(" ELSE t.data_type END data_type,");
        sql2.append(" CASE WHEN c.comments IS NULL THEN ''");
        sql2.append(" WHEN instr(c.comments, '##', 1, 1) IS NOT NULL");
        sql2.append(" AND instr(c.comments, '##', 1, 1) != 0 THEN");
        sql2.append(" TRIM(replace(substr(c.comments, 1, ");
        sql2.append("     instr(c.comments, '##', 1, 1) - 1), CHR(10), ''))");
        sql2.append(" WHEN instr(c.comments, '##', 1, 1) IS NOT NULL");
        sql2.append(" AND instr(c.comments, '##', 1, 1) != 0 THEN");
        sql2.append(" TRIM(replace(substr(c.comments, 1, ");
        sql2.append("     instr(c.comments, '##', 1, 1) - 1), CHR(10), ''))");
        sql2.append(" WHEN instr(c.comments, '(', 1, 1) IS NOT NULL");
        sql2.append(" AND instr(c.comments, '(', 1, 1) != 0 THEN");
        sql2.append(" TRIM(replace(substr(c.comments, 1, ");
        sql2.append("     instr(c.comments, '(', 1, 1) - 1), CHR(10), ''))");
        sql2.append(" WHEN instr(c.comments, '锛�', 1, 1) IS NOT NULL ");
        sql2.append(" AND instr(c.comments, '锛�', 1, 1) != 0 ");
        sql2.append(" THEN TRIM(replace(substr(c.comments, 1, ");
        sql2.append("     instr(c.comments, '锛�', 1, 1) - 1), CHR(10), ''))");
        sql2.append(" ELSE TRIM(replace(c.comments, CHR(10), '')) END comments,");
        sql2.append(" CASE WHEN t.data_type = 'DATE' OR substr(t.data_type, 1, 9) ");
        sql2.append("     = 'TIMESTAMP' THEN 30");
        sql2.append(" WHEN t.data_type = 'NUMBER' AND t.data_scale = 0 ");
        sql2.append(" THEN t.data_precision");
        sql2.append(" WHEN t.data_type = 'NUMBER'");
        sql2.append(" AND t.data_scale > 0 ");
        sql2.append(" THEN( t.data_precision + t.data_scale + 1 )");
        sql2.append(" WHEN t.data_length > 1 ");
        sql2.append(" THEN t.data_length / 2");
        sql2.append(" ELSE t.data_length END data_length,");
        sql2.append(" CASE WHEN t.data_type = 'NUMBER' AND t.data_scale = 0 ");
        sql2.append(" THEN ( lpad('9', t.data_precision, '9') )");
        sql2.append(" WHEN t.data_type = 'NUMBER' AND t.data_scale > 0 ");
        sql2.append(" THEN ( lpad('9', t.data_precision, '9') || ");
        sql2.append("     '.' || rpad('9', t.data_scale, '9') )");
        sql2.append(" END data_max,");
        sql2.append(" CASE WHEN t.data_type = 'NUMBER'");
        sql2.append(" AND t.data_scale > 0 THEN ( '0,000.' || rpad('0', t.data_scale, '0') )");
        sql2.append(" END data_format,");
        sql2.append(" t.data_precision,");
        sql2.append(" to_number(t.data_scale) data_scale,");
        sql2.append(" t.nullable");
        sql2.append(" FROM");
        sql2.append(" user_tab_cols       t,");
        sql2.append(" user_col_comments   c");
        sql2.append(" WHERE 1 = 1");
        sql2.append(" AND t.table_name = c.table_name");
        sql2.append(" AND t.column_name = c.column_name");
        sql2.append(" AND t.table_name = upper('");
        sql2.append(model.tablename);
        sql2.append("')");

        StringBuilder sql3 = new StringBuilder();
        sql3.append(" SELECT a.column_name");
        sql3.append(" FROM");
        sql3.append("  user_cons_columns   a,");
        sql3.append("  user_constraints    b");
        sql3.append(" WHERE");
        sql3.append(" a.constraint_name = b.constraint_name");
        sql3.append(" AND b.constraint_type = 'P'");
        sql3.append(" AND a.table_name = upper('");
        sql3.append(model.tablename);
        sql3.append("')");

        // 创建连接
        Connection con = null;
        PreparedStatement pStemt = null;
        try {
            Class.forName(dbConfig.DRIVER);
            con = DriverManager.getConnection(dbConfig.URL, dbConfig.USER, dbConfig.PASSWORD);
            pStemt = con.prepareStatement(sql1.toString());
            System.out.println("sql1===" + sql1);
            ResultSet rs = pStemt.executeQuery();
            boolean rsFlag = false;
            while (rs.next()) {
                table = setVo(Table.class, rs);
                rsFlag = true;
            }
            if (!rsFlag) {
                throw new ProcessorException("您输入的表名 【" + model.tablename + "】，在url【" + dbConfig.URL + "】user【" + dbConfig.USER + "】中不存在");
            }
            String primaryKey = "";

            pStemt = con.prepareStatement(sql3.toString());
            rs = pStemt.executeQuery();
            while (rs.next()) {
                primaryKey = rs.getObject("COLUMN_NAME").toString();
            }

            pStemt = con.prepareStatement(sql2.toString());
            System.out.println("sql2===" + sql2);
            rs = pStemt.executeQuery();

            while (rs.next()) {
                Column c = setVo(Column.class, rs);
                if (!Column.ignoreColumns.toLowerCase().contains(c.getvName().toLowerCase())) {
                    c.setVtypeByDType();
                    c.setXmlTypeByDtype();
                    c.setUNICODE_COMMENTS(UnicodeUtil.chinaToUnicode(c.getCOMMENTS()));
                    table.getColumns().add(c);
                }
                if (primaryKey.equals(rs.getObject("DNAME"))) {
                    c.setPrimary(true);
                } else {
                    c.setPrimary(false);
                }
            }
        } catch (Exception e1) {
            System.err.println(e1.getMessage() + model.dbConfig.URL);
            System.err.println(model.dbConfig.USER);
            System.err.println(model.dbConfig.PASSWORD);
            throw new ProcessorException(e1.getMessage());
        } finally {
            try {
                if (pStemt != null) {
                    pStemt.close();
                    con.close();
                }
            } catch (Exception e) {
            }
        }
        model.setTable(table);
        chain.handle(model);
    }

    public <T> T setVo(Class<T> c, ResultSet rs) throws InstantiationException, IllegalAccessException,
            IllegalArgumentException, SQLException {

        T instince = c.newInstance();
        Field[] fields = c.getDeclaredFields();
        for (Field f : fields) {
            try {
                if (1 == f.getModifiers()) {
                    f.setAccessible(true);
                    f.set(instince, String.valueOf(rs.getObject(f.getName())));
                }
            } catch (Exception e) {
            }
        }
        return instince;
    }
}
